# ---JD Stock Telent---

import datetime
import Core.IO as IO
import Core.MongoDB as MongoDB
import Core.Gadget as Gadget
import Core.Algorithm as Algorithm
import math
import numpy as np



#服务器地址变更后，也要修改Core文件下的Config文件里的IP地址

from Core.Config import *

def ProcessDateTime(strDateTime):
    pass

def LoadPortfolio(pathFileName, startRow=2):
    header, table = IO.ReadCSVFile(pathFileName)
    #
    count = 0
    for data in table:
        count = count + 1
        #
        if count + 1 < startRow:
            continue
        portfolioDoc = {}
        id = data[header["id"]]
        #
        portfolioDoc["UserName"] = data[header["name"]]
        portfolioDoc["Name"] = id
        portfolioDoc["Type"] = float(data[header["type"]])
        # portfolioDoc["CreatedTime"] = data[header["created_time"]]
        portfolioDoc["Pin"] = data[header["pin"]]
        portfolioDoc["Yn"] = float(data[header["yn"]])
        portfolioDoc["Disabled"] = float(data[header["disabled"]])
        portfolioDoc["Key"] = id
        database.upsert("Talent", "Portfolio", {"Key": portfolioDoc["Key"]}, portfolioDoc)


def LoadAccount(pathFileName, startRow=2):
    header, table = IO.ReadCSVFile(pathFileName)
    #
    count = 0
    for data in table:
        count = count + 1
        if count + 1 < startRow:
            continue
        # portfolio = data[header["user_pin"]]
        # type = data[header["type"]]
        id = data[header["package_id"]]
        dt = data[header["value_date"]]
        #
        print(str(count) + " " + id)
        if dt == "null":
            continue
        #
        dirtyDT = dt.split(" ")
        dt = dirtyDT[0]
        localDateTime = Gadget.ParseDateTime(dt, "%Y-%m-%d")
        localDateTime = localDateTime + datetime.timedelta(hours=15)
        stdDateTime = Gadget.ToUTCDateTime(localDateTime)
        strDateTime = Gadget.ToDateTimeString(localDateTime)

        #
        accountDoc = {}
        accountDoc["Portfolio"] = id
        accountDoc["StdDateTime"] = stdDateTime
        accountDoc["Value"] = float(data[header["value"]])
        accountDoc["ProfitLoss"] = float(data[header["income_d"]])
        accountDoc["Cash"] = 0.0
        accountDoc["DailyReturn"] = float(data[header["return_rate_d"]])
        accountDoc["UnitNetValue"] = 1.0
        accountDoc["Key"] = id + "_" + strDateTime

        # --- Insert Database---
        database.upsert("Talent", "Account", {"Key": accountDoc["Key"]}, accountDoc)
        kkwood = 0

def LoadTrade(pathFileName, startRow=2):
    header, table = IO.ReadCSVFile(pathFileName)
    #
    count = 0
    for data in table:
        count = count + 1
        if count + 1 < startRow:
            continue
        #
        tradeDoc = {}
        portfolio = data[header["package_id"]]
        fullcode = data[header["stock_code"]]
        market = fullcode[:2]
        code = fullcode[2:]
        symbol = code + "." + market
        #
        print(str(count) + " " + portfolio)
        #
        dt = data[header["trade_time"]]
        localDateTime = Gadget.ParseDateTime(dt, "%Y-%m-%d %H:%M:%S")
        stdDateTime = Gadget.ToUTCDateTime(localDateTime)
        strDateTime = Gadget.ToDateTimeString(localDateTime)

        #
        tradeDoc["Portfolio"] = portfolio
        tradeDoc["Symbol"] = symbol
        #
        qty = float(data[header["stock_number_to"]]) - float(data[header["stock_number_from"]])
        if qty >= 0:
            side = "Buy"
        else:
            side = "Sell"
        tradeDoc["Side"] = side
        tradeDoc["Qty"] = qty
        tradeDoc["Price"] = float(data[header["price"]])
        tradeDoc["CashFlow"] = -1 * qty * tradeDoc["Price"]
        #
        tradeDoc["Cost"] = float(data[header["cost_price"]])
        tradeDoc["StdDateTime"] = stdDateTime
        tradeDoc["Key"] = portfolio + "_" + symbol + "_" + side + "_" + strDateTime

        # --- Insert Database---
        database.upsert("Talent", "Trade", {"Key": tradeDoc["Key"]}, tradeDoc)
        kkwood = 0


def LoadProductOrder(pathFileName, startRow=2):
    header, table = IO.ReadExcelFile(pathFileName, "订单信息")
    #
    count = 0
    for data in table:
        count = count + 1
        if count + 1 < startRow:
            continue

        orderId = data[header["订单号"]]
        #
        print(str(count) + " " + orderId)
        if orderId == "null":
            continue
        #
        accountDoc = {}
        accountDoc["OrderID"] = orderId
        accountDoc["PIN"] = data[header["客户PIN"]]
        accountDoc["ProductID"] = data[header["商品编号"]]
        accountDoc["ProductType"] = data[header["商品编号"]][:3]
        accountDoc["ProductName"] = data[header["商品名称"]]
        accountDoc["TalentID"] = data[header["牛人ID"]]
        accountDoc["Status"] = data[header["支付状态"]]
        accountDoc["PaymentPrice"] = float(data[header["支付价格"]])

        dt = data[header["订单生成时间"]]
        accountDoc["DateTime"] = Gadget.ParseDateTime(dt, format="%Y-%m-%d %H:%M:%S")

        paymentDateTime = None
        if data[header["支付时间"]] != "":
            dt = data[header["支付时间"]]
            paymentDateTime = Gadget.ParseDateTime(dt, format="%Y-%m-%d %H:%M:%S")
        accountDoc["PaymentDateTime"] = paymentDateTime
        accountDoc["Key"] = orderId

        # --- Insert Database---
        database.upsert("Talent", "ProductOrder", {"Key": accountDoc["Key"]}, accountDoc)
        kkwood = 0


def LoadProductSettlement(pathFileName, startRow=2):
    header, table = IO.ReadExcelFile(pathFileName, "结算")
    #
    count = 0
    for data in table:
        count = count + 1
        if count + 1 < startRow:
            continue

        orderId = data[header["订单编号"]]
        #
        print(str(count) + " " + orderId)
        if orderId == "null":
            continue
        #
        accountDoc = {}
        accountDoc["OrderID"] = orderId
        accountDoc["ProductID"] = data[header["商品ID"]]
        accountDoc["ProductType"] = data[header["商品ID"]][:3]
        accountDoc["ProductName"] = data[header["商品名称"]]
        accountDoc["TalentID"] = data[header["商品所属牛人pin"]]
        accountDoc["SettlementID"] = data[header["对应结算单ID"]]
        accountDoc["SettlementStatus"] = data[header["结算状态"]]
        accountDoc["PaymentPrice"] = float(data[header["售出价格"]])

        dt = data[header["结算归属月份"]]
        accountDoc["DateTime"] = Gadget.ParseDateTime(dt, format="%Y-%m-%d")
        accountDoc["Key"] = orderId

        # --- Insert Database---
        database.upsert("Talent", "ProductSettlement", {"Key": accountDoc["Key"]}, accountDoc)
        kkwood = 0


def LoadOrderFlow(pathFileName, startRow=2):
    header, table = IO.ReadCSVFile(pathFileName)
    #
    count = 0
    for data in table:
        count = count + 1
        if count + 1 < startRow:
            continue

        orderId = data[header["order_id"]]
        #
        print(str(count) + " " + orderId)
        if orderId == "null":
            continue
        #
        accountDoc = {}
        accountDoc["id"] = data[header["id"]]
        accountDoc["order_id"] = orderId
        accountDoc["pin"] = data[header["pin"]]
        accountDoc["original_order_id"] = data[header["original_order_id"]]
        accountDoc["sku_code"] = data[header["sku_code"]]
        accountDoc["sku_type"] = data[header["sku_type"]]
        accountDoc["sku_name"] = data[header["sku_name"]]
        accountDoc["source"] = data[header["source"]]

        #---float---
        accountDoc["final_price"] = float(data[header["final_price"]])
        accountDoc["order_status"] = float(data[header["order_status"]])
        accountDoc["pay_status"] = float(data[header["pay_status"]])

        #---datetime---
        accountDoc["created_time"] = Gadget.ParseDateTime(data[header["created_time"]], format="%Y-%m-%d %H:%M:%S")
        accountDoc["modified_time"] = Gadget.ParseDateTime(data[header["modified_time"]], format="%Y-%m-%d %H:%M:%S")
        accountDoc["dt"] = Gadget.ParseDateTime(data[header["dt"]], format="%Y-%m-%d")

        paymentDateTime = None
        if data[header["pay_time"]] == "null" or data[header["pay_time"]] == "":
            accountDoc["pay_time"] = None
        else:
            paymentDateTime = Gadget.ParseDateTime(data[header["pay_time"]], format="%Y-%m-%d %H:%M:%S")


        #
        #accountDoc["Key"] = accountDoc["id"]

        # --- Insert Database---
        # database.upsert("Talent", "Orderflow", {"Key": accountDoc["Key"]}, accountDoc)
        database.insert("Talent", "Orderflow", accountDoc)
        kkwood = 0


def LoadPosition(pathFileName, tradeDate, startRow=2):
    #
    collectionName = "Position" + tradeDate
    database.creatIndex("Talent", collectionName, "Key")
    #
    tradeDate = Gadget.ParseDateTime(tradeDate, "%Y%m%d")
    tradeDateTime = tradeDate + datetime.timedelta(hours=15)
    strDateTime = Gadget.ToDateTimeString(tradeDateTime)
    utcDateTime = Gadget.ToUTCDateTime(tradeDateTime)
    #
    header, table = IO.ReadCSVFile(pathFileName)
    #
    count = 0
    for data in table:
        count = count + 1
        if count + 1 < startRow:
            continue

        # orderId = data[header["order_id"]]
        #
        print(str(count) + " ")
        # if orderId == "null":
        #     continue
        #
        positionDoc = {}
        positionDoc["Portfolio"] = data[header["package_id"]]

        #---SZ002345---
        dirtySymbol = data[header["stock_code"]]
        positionDoc["Symbol"] = dirtySymbol[-6:] + "." + dirtySymbol[:2]

        #---float---
        positionDoc["Cost"] = float(data[header["cost"]])
        positionDoc["Avalaible"] = float(data[header["salable_number"]])
        positionDoc["Qty"] = float(data[header["stock_number"]])

        #---datetime---
        positionDoc["DateTime"] = tradeDateTime
        positionDoc["StdDateTime"] = utcDateTime

        #---
        positionDoc["Key"] = positionDoc["Portfolio"] + "_" + positionDoc["Symbol"] + "_" + strDateTime

        # --- Insert Database---
        database.upsert("Talent", collectionName, {"Key": positionDoc["Key"]}, positionDoc)
        kkwood = 0


def CheckOrderflowTotalRange():
    beginDatetime = datetime.datetime(2017, 12, 1)
    for i in range(30):
        endingDateTime = beginDatetime + datetime.timedelta(days= 7 * (i + 1))
        print(endingDateTime)


def CheckTargetOrder():
    query = {}
    # query["created_time"] = {}
    # query["created_time"]["$lt"] = datetime.datetime(2018, 6, 22)
    # query["created_time"] = {}
    # query["created_time"]["$gte"] = datetime.datetime(2017,12,15)
    # query["created_time"]["$lte"] = datetime.datetime(2017, 12, 22)
    query["pin"] = "3a31bf681dc15599f92d1862645810ef"
    query["sku_code"] = "GJH-20171220092205-1-1270"
    orders = database.find("Talent", "Orderflow", query=query)
    for order in orders:
        uid = order["sku_code"] + "+" + order["pin"]
        print("Update " + uid + " Status " + str(order["pay_status"]) + " time " + str(
            order["modified_time"]) + " order " + order["order_id"])

    kkwood = 0

def PrintOrder(order, comment):
    uid = order["sku_code"] + "+" + order["pin"]
    print(comment + " " + str(order["id"]) + " " + uid + " Status " + str(order["pay_status"]) + " time " + str(
        order["modified_time"]) + " order " + order["order_id"])


def CheckOrderflow():

    orderByOrderID = {}
    orderByUID = {}

    #
    query = {}
    query["created_time"] = {}
    query["created_time"]["$lt"] = datetime.datetime(2018,6,22)
    #query["created_time"] = {}
    #query["created_time"]["$gte"] = datetime.datetime(2017,12,15)
    #query["created_time"]["$lte"] = datetime.datetime(2017, 12, 22)
    orders = database.find("Talent", "Orderflow", query=query)

    pin = "3a31bf681dc15599f92d1862645810ef"
    sku_code = "GJH-20171220092205-1-1270"
    testUid = sku_code + "+" + pin
    countEqualTime = 0
    greaterTimeLessId = 0
    for order in orders:
        if order["pay_status"] == 6:
            continue

        orderID = order["order_id"]
        #print(orderID)
        uid = order["sku_code"] + "+" + order["pin"]

        if uid not in orderByUID:
            notIUID = True
            orderByUID[uid] = order
        else:
            if uid == testUid:
                kkwood = 0

            greaterTime = False
            equalTime = False
            greaterId = False
            greaterStatus = False
            equalStatus = False
            if order["modified_time"] > orderByUID[uid]["modified_time"]:
               greaterTime = True
            if order["modified_time"] == orderByUID[uid]["modified_time"]:
                equalTime = True
                countEqualTime += 1
            if float(order["id"]) > float(orderByUID[uid]["id"]):
                greaterId = True
            if greaterId and not greaterTime:
                greaterTimeLessId += 1
            if float(order["pay_status"]) > float(orderByUID[uid]["pay_status"]):
                greaterStatus = True
            if order["pay_status"] == orderByUID[uid]["pay_status"]:
                equalStatus = True

            condition1 = False
            condition2 = False
            condition3 = False
            condition4 = False
            condition5 = False
            if order["modified_time"] >= orderByUID[uid]["modified_time"] and greaterId:
                condition1 = True
            if greaterId:
                condition2 = True
            if greaterTime or (equalTime and greaterId):
                condition3 = True
            if greaterTime or (equalTime and greaterStatus):
                condition4 = True
            if greaterTime or (equalTime and greaterStatus) or (equalTime and equalStatus and greaterId):
                condition5 = True

            if condition4 != condition5:
                if orderByUID[uid]["pay_status"] != order["pay_status"]:
                    PrintOrder(orderByUID[uid], "Old")
                    PrintOrder(order, "New")
                    kkwood = 0

            if condition5:
                orderByUID[uid] = order
                #print("Update")

                #if uid == testUid:
                #    PrintOrder(order, "Update")
        #if orderID not in orderByUID:
        #    notIUID = True
        #    orderByOrderID[orderID] = order
    testOrder = orderByUID[testUid]
    PrintOrder(testOrder, "Final")
    print(len(orderByUID))

    beginDatetime = datetime.datetime(2017, 12, 1)
    beginDatetime = Gadget.ToUTCDateTime(beginDatetime)
    beginDatetime = beginDatetime + datetime.timedelta(hours=+8)
    for i in range(30):
        endingDateTime = beginDatetime + datetime.timedelta(days=7)
        # print(endingDateTime)
        success = 0
        refund = 0
        refundPending = 0
        timeout = 0
        failed = 0
        for uid,order in orderByUID.items():
            if order["modified_time"] >= beginDatetime and  order["modified_time"] < endingDateTime:
                if order["pay_status"] == 2:
                    success += 1
                if order["pay_status"] == 3:
                    refundPending += 1
                if order["pay_status"] == 4:
                    refund += 1
                if order["pay_status"] == 5:
                    timeout += 1
                if order["pay_status"] == 6:
                    failed += 1

        #print("Week:" + str(i) + " Two:" + str(success) + " Three:" + str(refundPending) + " Four:" + str(refund) + " Five:" + str(timeout) + " Six:" + str(failed) )
        print(str(i) + " " + str(success) + " " + str(refundPending) + " " + str(refund) + " " + str(timeout) + " " + str(failed))
        beginDatetime = endingDateTime
    kkwood = 0


    beginDatetime = datetime.datetime(2017, 12, 1)
    for i in range(30):
        endingDateTime = beginDatetime + datetime.timedelta(days= 7 * (i + 1))
        print(endingDateTime)

def CheckNoneSettlement():

    query = {}
    query["ProductType"] = "GJH"
    datas = database.find("Talent","ProductSettlement", query=query)
    orderBook = {}

    settlementSum = 0
    for data in datas:
        orderId = data["OrderID"]
        settlementSum += data["PaymentPrice"]
        orderBook[orderId] = data

    print(len(orderBook))
    print("Settlement Money " + str(settlementSum))
    kkwood = 0

    query["Status"] = "支付成功"
    orders = database.find("Talent", "ProductOrder", query=query)

    paySum = 0
    notInOrderBook = 0
    for order in orders:
        paySum += order["PaymentPrice"]
        orderId = order["OrderID"]
        if orderId not in orderBook:
            print(order["OrderID"] + " " + order["ProductName"] + " " + order["PIN"])
            notInOrderBook += 1

    print("Payment Money " + str(paySum))
    print("notInOrderBook " + str(notInOrderBook))
    kkwood = 0

def CountTrades():
    #
    datetime1 = datetime.datetime(2015, 1, 1)
    datetime1 = Gadget.ToUTCDateTime(datetime1)
    datetime2 = datetime.datetime(2018, 5, 1)
    datetime2 = Gadget.ToUTCDateTime(datetime2)
    datetimes = Gadget.GenerateEndDayofMonth(datetime1, datetime2)
    #
    for dt in datetimes:
        # print(dt)
        datetime2 = dt
        data = database.find("Talent", "Trade", datetime1, datetime2)
        count = len(data)
        print(str(dt) + " " + str(count))

        #
        datetime1 = dt

def PorfolioPerformence():

    portfolios = database.find("Talent", "Portfolio", query={"Disabled":0})

    #
    datetime1 = datetime.datetime(2015, 11, 1)
    datetime1 = Gadget.ToUTCDateTime(datetime1)
    datetime2 = datetime.datetime(2018, 5, 1)
    datetime2 = Gadget.ToUTCDateTime(datetime2)
    datetimes = Gadget.GenerateEndDayofMonth(datetime1, datetime2)
    #
    for dt in datetimes:
        # print(dt)
        datetime2 = dt
        #
        rets = []
        for p in portfolios:
            packageId = p["Name"]
            data = database.find("Talent", "Account", datetime1, datetime2, query={"Portfolio":packageId})
            length = len(data)
            if length > 0:
                ret = data[length-1]["Value"] / data[0]["Value"] - 1
                if np.isnan(ret):
                    print(packageId)
                rets.append(ret)

        meanRets = np.mean(rets)
        print(str(dt) + " meanReturn " + str(meanRets))

        #
        datetime1 = dt

def TradesPerformence():
    #
    datetime1 = datetime.datetime(2015, 11, 1)
    datetime1 = Gadget.ToUTCDateTime(datetime1)
    datetime2 = datetime.datetime(2018, 5, 1)
    datetime2 = Gadget.ToUTCDateTime(datetime2)
    datetimes = Gadget.GenerateEndDayofMonth(datetime1, datetime2)
    #
    for dt in datetimes:
        # print(dt)
        datetime2 = dt
        data = database.find("Talent", "Trade", datetime1, datetime2)

        rets = []
        for trade in data:
            symbol = trade["Symbol"]
            stdDateTime = trade["StdDateTime"]
            nextWeek = stdDateTime + datetime.timedelta(days=8)
            nextMonth = stdDateTime + datetime.timedelta(days=31)
            #
            quote = database.find("Quote", symbol + "_Time_86400_Bar", stdDateTime, nextWeek)

            length = len(quote)
            if length > 0:
                ret = quote[length - 1]["Values"]["BClose"] / quote[0]["Values"]["BClose"] - 1
                if np.isnan(ret):
                    kkwood = 0
                rets.append(ret)

        meanRets = np.mean(rets)
        print(str(dt) + " meanReturn " + str(meanRets))

        #
        datetime1 = dt


# --- 1yr /2yr 周期下， 不同标准的选拔 ---
def SelectTopTalent():
    #
    portfolios = database.find("Talent", "Portfolio", query={"Disabled": 0})
    #
    datetime1 = datetime.datetime(2017, 5, 1)
    datetime1 = Gadget.ToUTCDateTime(datetime1)
    datetime2 = datetime.datetime(2018, 5, 1)
    datetime2 = Gadget.ToUTCDateTime(datetime2)

    data = []
    for p in portfolios:
        packageId = p["Name"]
        userName = p["UserName"].strip()
        type = p["Type"]
        dataSeries = database.find("Talent", "Account", datetime1, datetime2, query={"Portfolio": packageId})
        trades = database.find("Talent", "Trade", datetime1, datetime2, query={"Portfolio": packageId})
        length = len(dataSeries)
        if length > 0:
            ret = dataSeries[length - 1]["Value"] / dataSeries[0]["Value"] - 1
            ret = ret / length * 244
            if np.isnan(ret):
                print(packageId)
            #
            stdDev = Algorithm.CalcVolatility_DataSeries(dataSeries, "Value")
            if stdDev == None:
                stdDev = 0
            else:
                annulizedStdDev = stdDev * np.sqrt(240)
            if annulizedStdDev != 0:
                sharpe = ret/annulizedStdDev
            else:
                sharpe = 0
            #
            print(packageId + " UserName " + userName + " Type " + str(type) + " #Days " + str(length) + " #Trades " + str(len(trades)) + " Return " + str(ret) + " AnnulizedStdDev " + str(annulizedStdDev) + " Sharpe " + str(sharpe))
            kkwood = 0

        #data.append([valuation, capitalReturn, 0, 0, 0])


def PrintPositions(positions):
    print("Symbol,Qty,Price,Value,CashFlow,PL")
    for k,v in positions.items():
        print(k + " Qty " + str(v["Qty"]) + " Price " + str(v["Price"]) + " Value " + str(v["Value"]) + " CashFlow " + str(v["CashFlow"]) + " PL " + str(v["PL"]))


def CorrectAdjFactor(adjFactor, position):
    if "AdjFactor" not in position:
        position["AdjFactor"] = adjFactor
    if adjFactor != position["AdjFactor"]:
        position["Qty"] = position["Qty"] * (adjFactor / position["AdjFactor"])
        position["Cost"] = position["Cost"] * (position["AdjFactor"] / adjFactor)
        position["AdjFactor"] = adjFactor


def ValuePositions(positions, stdDateTime):
    #
    datetime1 = datetime.datetime(2016, 1, 1)
    datetime1 = Gadget.ToUTCDateTime(datetime1)
    #
    targetDate = False
    if datetime1.year == 2018 and datetime1.month == 9 and datetime1.day == 19:
        targetDate = True
    #
    totalPL = 0
    for symbol, position in positions.items():
        #
        if symbol == "300649.SZ":
            kkwood = 0

        if position["Qty"] != 0:
            quotes = database.find("Quote", symbol + "_Time_86400_Bar", datetime1, stdDateTime)
            count = len(quotes)
            if count > 0:
                quote = quotes[count - 1]
                # update price
                price = quote["Close"]
                position["Price"] = price

                # update adjusted factor && position qty
                CorrectAdjFactor(quote["AdjFactor"], position)

                # position value
                position["Value"] = price * position["Qty"]

                # position value + cum Cashflow
                position["PL"] = position["Value"] + position["CashFlow"]

            else:
                print(symbol + " Error Can't Find Price")
        #
        totalPL = totalPL + position["PL"]

    print(str(stdDateTime) + " totalPL " + str(totalPL))
    # PrintPositions(positions)
    print(" *** ")
    kkwood = 0


def BacktestTalentTrade(packageId, tradingCost=0, useAvgPrice=False):
    #
    datetime1 = datetime.datetime(2017, 5, 1)
    datetime1 = Gadget.ToUTCDateTime(datetime1)
    datetime2 = datetime.datetime(2018, 5, 1)
    datetime2 = Gadget.ToUTCDateTime(datetime2)
    #
    trades = database.find("Talent", "Trade", datetime1, datetime2, query={"Portfolio": packageId})

    positions = {}
    holdingPeriods = []
    tradeProfits = []
    statistics = []
    currentDateTime = None
    for trade in trades:
        # print(trade["Symbol"] + " " + str(trade["StdDateTime"]) + " " + trade["Side"] + " Qty " + str(trade["Qty"]) + " Price " + str(trade["Price"]))

        #
        tradeDateTime = trade["StdDateTime"]
        symbol = trade["Symbol"]

        # ---Update DateTime---
        tempDatetime = datetime.datetime(tradeDateTime.year, tradeDateTime.month, tradeDateTime.day, 15, 0, 0)
        if currentDateTime == None:
            currentDateTime = tempDatetime

        tradeDate = tempDatetime
        # ---Date Change---
        if tempDatetime > currentDateTime:
            # ---Value at Last Checkpoint---
            # ValuePositions(positions, currentDateTime)

            # ---Update Datetime, a New Day---
            currentDateTime = tempDatetime
            print(" Date " + str(currentDateTime))
            stdCurrentDateTime = Gadget.ToUTCDateTime(currentDateTime)

        # ---First buiild Position---
        if symbol not in positions:
            doc = {}
            doc["Symbol"] = symbol
            doc["DateTime"] = tradeDateTime
            doc["Cost"] = 0
            doc["Qty"] = 0
            doc["Price"] = 0
            doc["Value"] = 0
            doc["CashFlow"] = 0
            doc["PL"] = 0
            positions[symbol] = doc

        if tradeDateTime.year==2018 and tradeDateTime.month==2 and tradeDateTime.day==23:
            kkwood = 0

        if symbol == "600340.SH":
            kkwood = 0

        # --- Fair Price ---
        quotes = database.find("Quote", symbol + "_Time_86400_Bar", datetime1, tempDatetime)
        count = len(quotes)
        quote = quotes[count - 1]
        avgPrice = 0.25 * (quote["Open"] + quote["Close"] + quote["Low"] + quote["High"])
        #
        position = positions[symbol]
        discount = avgPrice / trade["Price"] - 1
        #
        if useAvgPrice:
            tradePrice = avgPrice
        else:
            tradePrice = trade["Price"]

        # ---update adjusted factor && position qty BEFORE trade---
        CorrectAdjFactor(quote["AdjFactor"], position)

        #
        #print(trade["Symbol"] + " " + str(trade["StdDateTime"]) + " " + trade["Side"] + " Qty " + str(
        #    trade["Qty"]) + " Price " + str(trade["Price"]) + " Discount : " + str(discount))
        #

        if trade["Side"] == "Buy":

            # ---Update Cost---
            if position["Qty"] == 0:
                position["Cost"] = 0
            position["Cost"] = (tradePrice * trade["Qty"] + position["Cost"] * position["Qty"]) / (trade["Qty"] + position["Qty"])
            if symbol == "600340.SH":
                print(symbol + ", Position: " + str(position["Qty"]) + ", Cashflow: " + str(position["CashFlow"]) + ", Cost: " + str(position["Cost"]))

            # ---update position---
            position["Qty"] = position["Qty"] + trade["Qty"]
            cashflow = -1 * tradePrice * (1 + tradingCost) * trade["Qty"]
            # ---Statistics---
            position["LastTradeDate"] = tradeDate

        #
        elif trade["Side"] == "Sell":
            qty = math.fabs(trade["Qty"])
            if qty > position["Qty"]:
                qty = position["Qty"]
            #
            position["Qty"] = position["Qty"] - qty
            cashflow = tradePrice * (1 - tradingCost) * qty

            # ---Statistics---
            if qty > 0:
                days = (tradeDate - position["LastTradeDate"]).days
                # holdingPeriods.append([symbol, days, cashflow])
                #
                profit = tradePrice / position["Cost"] - 1
                # tradeProfits.append([symbol, profit, cashflow])
                #
                if symbol == "600340.SH":
                    kkwood = 0
                #
                statistics.append([symbol, days, profit, cashflow, tradeDate])

        # ---cum cashflow---
        position["CashFlow"] = position["CashFlow"] + cashflow

        # ---update position value---
        position["Value"] = position["Qty"] * trade["Price"]

        # ---update pl = position value + cum cashflow---
        position["PL"] = position["Value"] + position["CashFlow"]
        kkwood = 0


    # ---Last day Valuation---
    maxDateTime = datetime.datetime(2018,5,7,15,0,0)
    maxDateTime = Gadget.ToUTCDateTime(maxDateTime)
    ValuePositions(positions, maxDateTime)
    print("PackageID:" + str(packageId))
    PrintPositions(positions)

    # ---Statistics---
    holdingDays = []
    profits = []
    weights = []
    for h in statistics:
        # print(h[0] + "," + str(h[1]) + "," + str(h[2]) + "," + str(h[3]) + "," + str(h[4]))
        holdingDays.append(h[1])
        profits.append(h[2])
        weights.append(h[3])

    #
    avgHoldingDays = np.average(holdingDays, weights=weights)
    medHoldingDays = np.median(holdingDays)
    #
    avgTradeProfits = np.average(profits, weights=weights)
    medTradeProfits = np.median(profits)

    print("Statistics")
    print("avgHoldingDays: " + str(avgHoldingDays) + " medHoldingDays " + str(medHoldingDays))
    print("avgTradeProfits: " + str(avgTradeProfits) + " medTradeProfits " + str(medTradeProfits))
    kkwood = 0


def BacktestTalentsTrade():
    #
    talentList = TalentList26_20180501()
    #
    for i in range(len(talentList)):
        #if i != 25:
        #    continue
        #
        if i < 1:
            continue
        #
        packageId = talentList[i]
        print(packageId)
        # BacktestTalentTrade(packageId, 3/1000, True)
        BacktestTalentTrade(packageId)
        kkwood = 1


def NetTradeStatistics(database, datetime1, datetime2, talentList):

    #
    positionBySymbol = {}
    #
    for packageId in talentList:
        trades = database.find("Talent", "Trade", datetime1, datetime2, query={"Portfolio": packageId})
        # ---Loop Trades---
        for trade in trades:
            symbol = trade["Symbol"]

            # ---Position Cache---
            if symbol not in positionBySymbol:
                doc = {}
                doc["Symbol"] = symbol
                # doc["DateTime"] = tradeDateTime
                # doc["Cost"] = 0
                doc["Qty"] = 0
                # doc["Price"] = 0
                # doc["Value"] = 0
                doc["CashFlow"] = 0
                # doc["PL"] = 0
                positionBySymbol[symbol] = doc
            position = positionBySymbol[symbol]

            #
            position["Qty"] += trade["Qty"]
            position["CashFlow"] += trade["CashFlow"]

    # ---Filter Zero---
    for symbol in list(positionBySymbol):
        position = positionBySymbol[symbol]
        if position["Qty"] == 0:
            positionBySymbol.pop(symbol)

    #
    for symbol, position in positionBySymbol.items():
        print(symbol + " " + str(position["Qty"]) + " " + str(-1 * position["CashFlow"]))

    kkwood = 0


def TalentList16_20180501():
    talentList = []
    #talentList.append(str(11175))
    #talentList.append(str(11373))
    #talentList.append(str(12195))
    #talentList.append(str(12304))
    #talentList.append(str(12419))
    talentList.append(str(11370))
    #talentList.append(str(12036))
    #talentList.append(str(11313))
    #talentList.append(str(12404))
    talentList.append(str(12495))
    talentList.append(str(12341))
    #talentList.append(str(12103))
    talentList.append(str(12334))
    talentList.append(str(12308))
    talentList.append(str(12230))
    talentList.append(str(11500))
    talentList.append(str(12279))
    talentList.append(str(11354))
    talentList.append(str(11147))
    talentList.append(str(11303))
    talentList.append(str(12340))
    talentList.append(str(12126))
    talentList.append(str(11556))
    #talentList.append(str(11251))
    talentList.append(str(12044))
    talentList.append(str(11738))
    return talentList


def TalentList26_20180501():
    talentList = []
    talentList.append(str(11175))
    talentList.append(str(11373))
    talentList.append(str(12195))
    talentList.append(str(12304))
    talentList.append(str(12419))
    talentList.append(str(11370))
    talentList.append(str(12036))
    talentList.append(str(11313))
    talentList.append(str(12404))
    talentList.append(str(12495))
    talentList.append(str(12341))
    talentList.append(str(12103))
    talentList.append(str(12334))
    talentList.append(str(12308))
    talentList.append(str(12230))
    talentList.append(str(11500))
    talentList.append(str(12279))
    talentList.append(str(11354))
    talentList.append(str(11147))
    talentList.append(str(11303))
    talentList.append(str(12340))
    talentList.append(str(12126))
    talentList.append(str(11556))
    talentList.append(str(11251))
    talentList.append(str(12044))
    talentList.append(str(11738))
    return talentList


def StyleBox():
    #
    positions = {}
    #
    talentList = TalentList16_20180501()
    for talent in talentList:
        query = {}
        query["Portfolio"] = talent
        positionData = database.find("Talent", "Position20180621", query=query)
        kkwood


# ---Build Table---
# database.creatIndex("Talent", "Portfolio", "Key")
#
# database.creatIndex("Talent", "Account", "Key")
# database.creatIndex("Talent", "Account", "Portfolio")
# database.creatIndex("Talent", "Account", "StdDateTime")
#
# database.creatIndex("Talent", "Trade", "Key")
# database.creatIndex("Talent", "Trade", "Portfolio")
# database.creatIndex("Talent", "Trade", "StdDateTime")

# database.creatIndex("Talent", "ProductOrder", "OrderID")
# database.creatIndex("Talent", "ProductOrder", "DateTime")

# database.creatIndex("Talent", "ProductSettlement", "OrderID")
# database.creatIndex("Talent", "ProductSettlement", "DateTime")
from Core.Config import Config
config = Config()
database = config.DataBase()
# LoadProductOrder("C:/Users/fengshimeng3/Documents/BigData/Order_Check/订单信息.xls")
# LoadProductSettlement("C:/Users/fengshimeng3/Documents/BigData/Order_Check/订单信息.xls")
# LoadOrderFlow("D:/Data/JDStockTalent/Orderflow20180703.csv")
LoadPosition(r"C:\Users\zhujunheng\Desktop\position_20181130.csv", "20181129")
#LoadPortfolio(r"C:\Users\zhujunheng\Desktop\portfolio_20181116.csv")
LoadAccount(r"C:\Users\zhujunheng\Desktop\account_20181130.csv", 333048)
LoadTrade(r"C:\Users\zhujunheng\Desktop\trade_20181130.csv", 181898)
